/***********************************************************************************************************************************
 *    Name: Trace_ChangeAudit.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Who's making changes to the database?
**/
DECLARE
  @TraceID		INT,
  @TraceFile	NVARCHAR(245)	= DBAdmin.GetInstancePath(N'WorkingDirectory') + @@SERVERNAME + N'_Trace_ChangeAudit',
  @StopTime		DATETIME		= DATEADD(dd,1,GETDATE()),
-- variable declarations required for data type
  @MaxFileSize	BIGINT			= 128,
  @On			BIT				= 1;

-- create the trace ============================================================
EXECUTE sp_trace_create
	@TraceID	= @TraceID OUTPUT,
	@options	= 2,				-- TRACE_FILE_ROLLOVER
	@tracefile	= @TraceFile,
	@maxfilesize= @MaxFileSize,
	@stoptime	= @StopTime,
	@filecount	= 8;				-- 8 files x 128MB = 1GB Max
/*
 *	Return	Description
 *	0		No error.
 *	1		Unknown error.
 *	10		Invalid options. Returned when options specified are incompatible.
 *	12		File not created.
 *	13		Out of memory. Returned when there is not enough memory to perform the specified action.
 *	14		Invalid stop time. Returned when the stop time specified has already happened.
 *	15		Invalid parameters. Returned when the user supplied incompatible parameters.
 */

-- set the events ==============================================================

--RPC:Starting (11) 
	EXECUTE sp_trace_setevent @TraceID, 11,  1, @On;	-- TextData
--	EXECUTE sp_trace_setevent @TraceID, 11,  3, @On;	-- DatabaseID 
	EXECUTE sp_trace_setevent @TraceID, 11,  8, @On;	-- HostName
	EXECUTE sp_trace_setevent @TraceID, 11, 10, @On;	-- ApplicationName
	EXECUTE sp_trace_setevent @TraceID, 11, 11, @On;	-- LoginName 
--	EXECUTE sp_trace_setevent @TraceID, 11, 12, @On;	-- SPID 
--	EXECUTE sp_trace_setevent @TraceID, 11, 13, @On;	-- Duration
--	EXECUTE sp_trace_setevent @TraceID, 11, 14, @On;	-- StartTime
--	EXECUTE sp_trace_setevent @TraceID, 11, 15, @On;	-- EndTime
--	EXECUTE sp_trace_setevent @TraceID, 11, 16, @On;	-- Reads
--	EXECUTE sp_trace_setevent @TraceID, 11, 17, @On;	-- Writes
--	EXECUTE sp_trace_setevent @TraceID, 11, 18, @On;	-- CPU
	EXECUTE sp_trace_setevent @TraceID, 11, 35, @On;	-- DatabaseName

--SQL:BatchStarting (13) 
	EXECUTE sp_trace_setevent @TraceID, 13,  1, @On;	-- TextData
--	EXECUTE sp_trace_setevent @TraceID, 13,  3, @On;	-- DatabaseID 
	EXECUTE sp_trace_setevent @TraceID, 13,  8, @On;	-- HostName
	EXECUTE sp_trace_setevent @TraceID, 13, 10, @On;	-- ApplicationName
	EXECUTE sp_trace_setevent @TraceID, 13, 11, @On;	-- LoginName 
--	EXECUTE sp_trace_setevent @TraceID, 13, 12, @On;	-- SPID 
--	EXECUTE sp_trace_setevent @TraceID, 13, 13, @On;	-- Duration
--	EXECUTE sp_trace_setevent @TraceID, 13, 14, @On;	-- StartTime
--	EXECUTE sp_trace_setevent @TraceID, 13, 15, @On;	-- EndTime
--	EXECUTE sp_trace_setevent @TraceID, 13, 16, @On;	-- Reads
--	EXECUTE sp_trace_setevent @TraceID, 13, 17, @On;	-- Writes
--	EXECUTE sp_trace_setevent @TraceID, 13, 18, @On;	-- CPU
	EXECUTE sp_trace_setevent @TraceID, 13, 35, @On;	-- DatabaseName

-- filter ======================================================================
EXECUTE sp_trace_setfilter @TraceID, 1, 1, 6, N'INSERT';
EXECUTE sp_trace_setfilter @TraceID, 1, 1, 6, N'UPDATE';
EXECUTE sp_trace_setfilter @TraceID, 1, 1, 6, N'DELETE';

--EXECUTE sp_trace_setfilter
--	@TraceID			= @TraceID,
--	@columnid			= 11,	-- LoginName
--	@logical_operator	= 0,	-- 0=AND;1=OR
--	@comparison_operator= 0,
--		-- 0	=  (Equal)
--		-- 1	<> (Not Equal)
--		-- 2	>  (Greater Than)
--		-- 3	<  (Less Than)
--		-- 4	>= (Greater Than or Equal)
--		-- 5	<= (Less Than or Equal)
--		-- 6	LIKE
--		-- 7	NOT LIKE
--	@value				= N'';

--EXECUTE sp_trace_setfilter
--	@TraceID			= @TraceID,
--	@columnid			= 3,	-- DatabaseID
--	@logical_operator	= 0,	-- 0=AND;1=OR
--	@comparison_operator= 0,
--		-- 0	=  (Equal)
--		-- 1	<> (Not Equal)
--		-- 2	>  (Greater Than)
--		-- 3	<  (Less Than)
--		-- 4	>= (Greater Than or Equal)
--		-- 5	<= (Less Than or Equal)
--		-- 6	LIKE
--		-- 7	NOT LIKE
--	@value				= 1;

-- start the trace
EXECUTE sp_trace_setstatus @TraceID, 1;
GO

-- check trace status
DECLARE @property TABLE (PropertyID INT NOT Null PRIMARY KEY CLUSTERED, PropertyText VARCHAR(9) NOT Null);
INSERT INTO @property VALUES (1,'Options'),(2,'File Name'),(3,'Max Size'),(4,'Stop Time'),(5,'Status');
SELECT TraceID, PropertyText AS Property, Value FROM fn_trace_getinfo(default) i INNER JOIN @property p ON i.Property = p.PropertyID;
GO

-- stop the trace
/*
DECLARE @TraceID INT= 0;
EXECUTE sp_trace_setstatus @TraceID, 0;	-- Stop
EXECUTE sp_trace_setstatus @TraceID, 2;	-- Close
 */